﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using Pms.Utils;
using Biblioteca.Utils;
using System.ComponentModel;

namespace SERIX.Classes
{
    [DataObject(true)]
    public static class CPlanejamento
    {
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterPlanejamentos(string login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"select IDT_PLO, ANO_PLO, planejamento_plo, programacao_plo, execucao_plo, QTDATENDIDAPROGRAMACAO_PLO
from srx_Planejamento
order by ANO_PLO";
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                //command.Parameters.Add(new OracleParameter("IDT_CLA_IND", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("ANO_AIS", OracleType.Number));
                //command.Parameters["IDT_CLA_IND"].Value = IDT_CLA_IND;
                //command.Parameters["IDT_PROG"].Value = IDT_PROG;
                //command.Parameters["IDT_ACAO"].Value = IDT_ACAO;
                //command.Parameters["ANO_AIS"].Value = ANO_AIS;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static void Insert(int ANO_PLO, string login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"insert into srx_planejamento (idt_PLO, ano_plo, planejamento_plo, programacao_plo, execucao_plo)
values((select nvl(max(IDT_PLO),0) from Serix.srx_planejamento)+1,
:ano_plo, 0, 0, 0)";
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("ano_plo", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("ANO_AIS", OracleType.Number));
                command.Parameters["ano_plo"].Value = ANO_PLO;
                //command.Parameters["IDT_PROG"].Value = IDT_PROG;
                //command.Parameters["IDT_ACAO"].Value = IDT_ACAO;
                //command.Parameters["ANO_AIS"].Value = ANO_AIS;
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static void ManipularPlanejamento(int IDT_PLO, bool planejamento_plo, string login)
        {
            OracleCommand command = new OracleCommand();
            try
            {
                string sql;
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.Transaction = command.Connection.BeginTransaction();
                command.CommandType = CommandType.Text;                
                if (planejamento_plo)//Fazer Validação
                {
                    sql = @"update srx_planejamento set
  planejamento_plo = '0'";
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                sql = @"update srx_planejamento set
planejamento_plo = :planejamento_plo
where IDT_PLO = :IDT_PLO";
                command.Parameters.Add(new OracleParameter("IDT_PLO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("planejamento_plo", OracleType.Number));
                command.Parameters["IDT_PLO"].Value = IDT_PLO;
                command.Parameters["planejamento_plo"].Value = planejamento_plo;
                command.CommandText = sql;
                command.ExecuteNonQuery();

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        public static void DefinirQtdPlanejada(int IDT_PLO, int qtd, string login)
        {
            string sql = @"update srx_planejamento
set QTDATENDIDAPROGRAMACAO_PLO = :QTDATENDIDAPROGRAMACAO_PLO
where IDT_PLO = :IDT_PLO";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.Parameters.Add(new OracleParameter("IDT_PLO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("QTDATENDIDAPROGRAMACAO_PLO", OracleType.Number));
                command.Parameters["IDT_PLO"].Value = IDT_PLO;
                command.Parameters["QTDATENDIDAPROGRAMACAO_PLO"].Value = qtd;
                command.CommandText = sql;
                command.ExecuteNonQuery();
                
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static int ObterQtdPlanejada(int ano_PLO, string login)
        {
            string sql = @"select QTDATENDIDAPROGRAMACAO_PLO from srx_planejamento
where ano_PLO = :ano_PLO";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.Parameters.Add(new OracleParameter("ano_PLO", OracleType.Number));
                command.Parameters["ano_PLO"].Value = ano_PLO;                
                command.CommandText = sql;
                Object obj = command.ExecuteScalar();
                if (obj != null)
                    return Convert.ToInt32(obj);
                return -1;

            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static void ManipularProgramacao(int IDT_PLO, bool programacao_plo, string login)
        {
            OracleCommand command = new OracleCommand();
            try
            {
                string sql;
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.Transaction = command.Connection.BeginTransaction();
                command.CommandType = CommandType.Text;                
                if (programacao_plo)//Fazer Validação
                {
                    sql = @"update srx_planejamento set
  programacao_plo = '0'";
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                sql = @"update srx_planejamento set
programacao_plo = :programacao_plo
where IDT_PLO = :IDT_PLO";
                command.Parameters.Add(new OracleParameter("IDT_PLO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("programacao_plo", OracleType.Number));
                command.Parameters["IDT_PLO"].Value = IDT_PLO;
                command.Parameters["programacao_plo"].Value = programacao_plo;
                command.CommandText = sql;
                command.ExecuteNonQuery();

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static void ManipularExecucao(int IDT_PLO, bool execucao_plo, string login)
        {
            OracleCommand command = new OracleCommand();
            try
            {
                string sql;
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.Transaction = command.Connection.BeginTransaction();
                command.CommandType = CommandType.Text;
                if (execucao_plo)//Fazer Validação
                {
                    sql = @"update srx_planejamento set
  execucao_plo = '0'";
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                sql = @"update srx_planejamento set
execucao_plo = :execucao_plo
where IDT_PLO = :IDT_PLO";
                command.Parameters.Add(new OracleParameter("IDT_PLO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("execucao_plo", OracleType.Number));
                command.Parameters["IDT_PLO"].Value = IDT_PLO;
                command.Parameters["execucao_plo"].Value = execucao_plo;
                command.CommandText = sql;
                command.ExecuteNonQuery();

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static int AnoPlanejamentoCorrente(string login)
        {
            string sql = @"select ano_Plo from srx_planejamento
where planejamento_plo = '1'";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                Object obj = command.ExecuteScalar();
                if (obj != null)
                    return Convert.ToInt32(obj);
                return -1;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }

        public static int AnoProgramacaoCorrente(string login)
        {
            string sql = @"select ano_Plo from srx_planejamento
where programacao_plo = '1'";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                Object obj = command.ExecuteScalar();
                if (obj != null)
                    return Convert.ToInt32(obj);
                return -1;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }

        public static int AnoExecucaoCorrente(string login)
        {
            string sql = @"select ano_Plo from srx_planejamento
where EXECUCAO_PLO = '1'";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                Object obj = command.ExecuteScalar();
                if (obj != null)
                    return Convert.ToInt32(obj);
                return -1;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }


        public static bool AnoLiberadoPlanejamento(int ano_Plo, string login)
        {
            return AnoPlanejamentoCorrente(login) == ano_Plo;
        }

        public static bool AnoLiberadoProgramacao(int ano_Plo, string login)
        {
            return AnoProgramacaoCorrente(login) == ano_Plo;
        }

        public static bool AnoLiberadoExecucao(int ano_Plo, string login)
        {
            return AnoExecucaoCorrente(login) == ano_Plo;
        }

        public static int ObterQtdMinimaProgramacao(string ano_Plo, string login)
        {
            string sql = @"select QTDATENDIDAPROGRAMACAO_PLO from srx_planejamento
where ano_plo = :ano_plo";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new OracleParameter("ano_plo", OracleType.Number));
                command.Parameters["ano_plo"].Value = ano_Plo;

                command.CommandText = sql;
                Object obj = command.ExecuteScalar();
                if (obj != null)
                    return Convert.ToInt32(obj);
                return -1;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }
    }
}
